﻿using System;
using System.Collections.Generic;
using System.Text;
using NPOI.HSSF.UserModel;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Resources;



namespace InspectAllWithNPOI
{
    class Program
    {
        static void Main(string[] args)
        {
            //string savePath = string.Empty;

            //if (args.Length != 1)
            //{
            //    Console.Out.WriteLine("InspectAllWithNPOI.exe takes 1 parameter.");
            //    Console.Out.WriteLine("e.g. InspectAllWithNPOI [Out Put Path]");
            //    return;
            //}
            //else
            //{
            //    savePath = args[0];
            //}

            ////savePath = @"c:\mynew.xls";

            //MemoryStream ms = new MemoryStream(Resource1.my2);
            //HSSFWorkbook wb = new HSSFWorkbook(ms);

            //HSSFSheet RawDataFromDb = wb.GetSheet("RawDataFromDb") as HSSFSheet;
            //HSSFSheet report = wb.GetSheet("Report") as HSSFSheet;

            //DataSet ds = ReadDataFromDB();

            //System.Data.DataTable dbDT = ds.Tables["InspectAll"];

            //HSSFRow row = null;

            //for (int iRow = 0; iRow < dbDT.Rows.Count; iRow++)
            //{
            //    row = RawDataFromDb.CreateRow(iRow + 1) as HSSFRow;
            //    for (int jColumn = 0; jColumn < dbDT.Columns.Count; jColumn++)
            //    {
            //        if (jColumn == 0)
            //        {
            //            row.CreateCell(jColumn).SetCellValue(Convert.ToDateTime(dbDT.Rows[iRow][jColumn]).ToShortDateString());
            //            continue;
            //        }
            //        if (jColumn == 3)
            //        {
            //            row.CreateCell(jColumn).SetCellValue(Convert.ToInt32(dbDT.Rows[iRow][jColumn]));
            //            continue;
            //        }
            //        row.CreateCell(jColumn).SetCellValue(dbDT.Rows[iRow][jColumn].ToString());
            //    }
            //}

            //dbDT = ds.Tables["last_pp_inspection"];


            //for (int i = 0; i < dbDT.Rows.Count; i++)
            //{
            //    row = report.GetRow(i + 2) as HSSFRow;

            //    for (int j = 0; j < dbDT.Columns.Count; j++)
            //    {
            //        row.Cells[j].SetCellValue(Convert.ToDateTime(dbDT.Rows[i][j]).ToShortDateString());
            //    }
            //}


            //RawDataFromDb.ForceFormulaRecalculation = true;
            //report.ForceFormulaRecalculation = true;


            //FileStream file = new FileStream(savePath, FileMode.Create);


            ////if (File.Exists(savePath))
            ////{
            ////    File.Delete(savePath);
            ////}

            //wb.Write(file);
            //file.Close();
            //wb.Dispose();

            string templatePath = string.Empty;
            string savePath = string.Empty;

            if (args.Length != 2)
            {
                Console.Out.WriteLine("InspectAllWithNPOI.exe takes 2 parameter.");
                Console.Out.WriteLine("e.g. InspectAllWithNPOI [template Path] [out put path]");
                return;
            }
            else
            {
                templatePath = args[0];
                savePath = args[1];
            }


            FileStream fs = null;
            HSSFWorkbook wb = null;

            try
            {
                fs = new FileStream(templatePath, FileMode.Open, FileAccess.ReadWrite);
                wb = new HSSFWorkbook(fs);

                HSSFSheet RawDataFromDb = wb.GetSheet("RawDataFromDb") as HSSFSheet;
                HSSFSheet report = wb.GetSheet("Report") as HSSFSheet;

                DataSet ds = ReadDataFromDB();

                System.Data.DataTable dbDT = ds.Tables["InspectAll"];

                HSSFRow row = null;

                for (int iRow = 0; iRow < dbDT.Rows.Count; iRow++)
                {
                    row = RawDataFromDb.CreateRow(iRow + 1) as HSSFRow;
                    for (int jColumn = 0; jColumn < dbDT.Columns.Count; jColumn++)
                    {
                        if (jColumn == 0)
                        {
                            row.CreateCell(jColumn).SetCellValue(Convert.ToDateTime(dbDT.Rows[iRow][jColumn]).ToShortDateString());
                            continue;
                        }
                        if (jColumn == 3)
                        {
                            row.CreateCell(jColumn).SetCellValue(Convert.ToInt32(dbDT.Rows[iRow][jColumn]));
                            continue;
                        }
                        row.CreateCell(jColumn).SetCellValue(dbDT.Rows[iRow][jColumn].ToString());
                    }
                }

                dbDT = ds.Tables["last_pp_inspection"];


                for (int i = 0; i < dbDT.Rows.Count; i++)
                {
                    row = report.GetRow(i + 2) as HSSFRow;

                    for (int j = 0; j < dbDT.Columns.Count; j++)
                    {
                        row.Cells[j].SetCellValue(Convert.ToDateTime(dbDT.Rows[i][j]).ToShortDateString());
                    }
                }


                RawDataFromDb.ForceFormulaRecalculation = true;
                report.ForceFormulaRecalculation = true;

                fs.Close();
                fs.Dispose();

                fs = new FileStream(savePath, FileMode.Create, FileAccess.Write);
                wb.Write(fs);
            }
            catch (Exception ex)
            {
                Console.Out.WriteLine(ex.Message);
            }
            finally
            {
                if (fs != null)
                {
                    fs.Flush();
                    fs.Close();
                    fs.Dispose();
                }
                wb.Dispose();
            }


        }

        public static DataSet ReadDataFromDB()
        {
            string connStr = "Data Source=TK2MPBISQLREP1;Integrated Security=SSPI;Initial Catalog = Scratch";
            //string connStr = "Data Source=.;Integrated Security=SSPI;Initial Catalog = test";
            DataSet ds = new DataSet();

            SqlConnection conn = null;
            SqlDataAdapter adapter = null;
            SqlDataAdapter adapter2 = null;

            DateTime endDate = DateTime.UtcNow.Date;
            //DateTime startDate = endDate.AddDays(-100);
            DateTime startDate = endDate.AddDays(-7);
            try
            {
                conn = new SqlConnection(connStr);
                conn.Open();

                SqlCommand cmdPeriodic_Processing_Failures = new SqlCommand("Select Cast(RunDate as Date) as RunDate, 'PPFailure' as sourceField, Cast(Failure_Type as Varchar(100)) as Failure_Type, Count(*) as [Count] From dbo.Periodic_Processing_Failures (nolock) Where RunDate between @startDate and @endDate Group By Cast(RunDate as Date), Cast(Failure_Type as Varchar(100)) ORDER BY RunDate", conn);


                cmdPeriodic_Processing_Failures.CommandTimeout = 3600;
                cmdPeriodic_Processing_Failures.CommandType = CommandType.Text;

                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@startDate", startDate), new SqlParameter("@endDate", endDate) };


                cmdPeriodic_Processing_Failures.Parameters.AddRange(paras);


                adapter = new SqlDataAdapter(cmdPeriodic_Processing_Failures);
                adapter2 = new SqlDataAdapter("select * from last_pp_inspection(nolock)", conn);

                adapter.Fill(ds, "InspectAll");
                adapter2.Fill(ds, "last_pp_inspection");
            }
            catch (Exception ex)
            {
                Console.Out.WriteLine(ex.Message);
            }
            finally
            {
                if (adapter != null)
                    adapter.Dispose();
                if (adapter2 != null)
                    adapter2.Dispose();
                if (conn != null)
                    conn.Dispose();
            }
            return ds;
        }
    }
}
